import sqlite3
from typing import Optional

from fit_py_ask_number_demo.data_managers.database_manager import DatabaseManager


class SQLiteManager(DatabaseManager):
    def __init__(self, db_path: str):
        """
        初始化 SQLite 数据库连接
        :param db_path: 数据库文件路径
        """
        self.db_path = db_path
        self.connection: Optional[sqlite3.Connection] = None

    def connect(self):
        """连接到数据库"""
        self.connection = sqlite3.connect(self.db_path)

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

    def create(self, table: str, data: dict) -> bool:
        """
        插入一条数据
        :param table: 表名
        :param data: 数据字典，键为列名，值为数据
        :return: 是否成功
        """
        if not self.connection:
            self.connect()

        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?'] * len(data))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        values = tuple(data.values())

        try:
            cursor = self.connection.cursor()
            cursor.execute(query, values)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"插入数据失败: {e}")
            return False

    def read(self, table: str, condition: str = None) -> list[dict]:
        """
        查询数据
        :param table: 表名
        :param condition: 查询条件（可选）
        :return: 查询结果列表，每行数据为一个字典
        """
        if not self.connection:
            self.connect()

        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"

        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            rows = cursor.fetchall()
            columns = [column[0] for column in cursor.description]
            return [dict(zip(columns, row)) for row in rows]
        except sqlite3.Error as e:
            print(f"查询数据失败: {e}")
            return []

    def update(self, table: str, data: dict, condition: str) -> bool:
        """
        更新数据
        :param table: 表名
        :param data: 更新的数据字典，键为列名，值为新数据
        :param condition: 更新条件
        :return: 是否成功
        """
        if not self.connection:
            self.connect()

        set_clause = ', '.join([f"{key} = ?" for key in data.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE {condition}"
        values = tuple(data.values())

        try:
            cursor = self.connection.cursor()
            cursor.execute(query, values)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"更新数据失败: {e}")
            return False

    def delete(self, table: str, condition: str) -> bool:
        """
        删除数据
        :param table: 表名
        :param condition: 删除条件
        :return: 是否成功
        """
        if not self.connection:
            self.connect()

        query = f"DELETE FROM {table} WHERE {condition}"

        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"删除数据失败: {e}")
            return False